﻿using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Core.Entity;
using Wedn.Interface.DAL;
using Wedn.Utility;

namespace Wedn.DAL.MSSQL
{
    /// <summary>
    /// 元数据表操作类
    /// </summary>
    public class MetaDAO : IMeta
    {
        /// <summary>
        /// 向拓展元数据表中插入一行新数据, 返回执行结果受影响行数
        /// </summary>
        /// <param name="meta">要插入的对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int Insert(Meta meta)
        {
            string sql = @"INSERT INTO [&#WednTablePrefix%&_Metas]
                                    (
                                        [object_id],
                                        [object_type],
                                        [meta_key],
                                        [meta_value]
                                    )
                                   VALUES 
                                    (
                                        @ObjectId,
                                        @ObjectType,
                                        @Key,
                                        @Value
                                    );";
            return ExecuteSql(meta, sql);
        }

        /// <summary>
        /// 根据元数据ID删除对应的元数据记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要删除记录的元数据ID</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int id)
        {
            string sql = "DELETE FROM [&#WednTablePrefix%&_Metas] WHERE [meta_id]=@Id";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 根据元数据ID数组批量删除对应的元数据记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">要删除记录的ID数组</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int[] ids)
        {
            string ps = string.Join(",", ids);
            string sql = "DELETE FROM [&#WednTablePrefix%&_Metas] WHERE [meta_id] in (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 根据关系对象ID和类型删除对应的元数据记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="objectId">关系对象ID</param>
        /// <param name="objectType">关系对象类型(0:Post, 1:User, 2:Link, 3:Comment)</param>
        /// <param name="key">属性名称(省略该参数传入null, 默认删除全部属性)</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int objectId, int objectType, string key)
        {
            string sql = "DELETE FROM [&#WednTablePrefix%&_Metas] WHERE [object_id]=@Id AND [object_type]=@Type";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " AND [meta_key]=@Key;";
                return SqlHelper.ExecuteNonQuery(sql,
                    new SqlParameter("@Id", objectId),
                    new SqlParameter("@Type", objectType),
                    new SqlParameter("@Key", key));
            }
            else
            {
                return SqlHelper.ExecuteNonQuery(sql,
                    new SqlParameter("@Id", objectId),
                    new SqlParameter("@Type", objectType));
            }
        }

        /// <summary>
        /// 更新一个完整的拓展元数据对象, 返回执行结果受影响行数
        /// </summary>
        /// <param name="meta">要更新的对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateMeta(Meta meta)
        {
            string sql = @"UPDATE [&#WednTablePrefix%&_Metas]
                                   SET
                                        [object_id]=@ObjectId,
                                        [object_type]=@ObjectType,
                                        [meta_key]=@Key,
                                        [meta_value]=@Value
                                   WHERE
                                        [meta_id]=@Id;";
            return ExecuteSql(meta, sql);
        }

        /// <summary>
        /// 获取表中主键最大值, 返回主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public int SelectMaxId()
        {
            return SqlHelper.SelectMaxId("meta_id", "&#WednTablePrefix%&_Metas");
        }

        /// <summary>
        /// 检查表中对应的关系对象是否已经存在该属性名称, 返回一个布尔值, 存在则为True; 不存在则为False
        /// </summary>
        /// <param name="objectId">关联对象ID</param>
        /// <param name="objectType">关系对象类型(0:Post, 1:User, 2:Link, 3:Comment)</param>
        /// <param name="key">要确定的属性名称</param>
        /// <returns>存在返回True; 不存在返回False</returns>
        public bool Exists(int objectId, int objectType, string key)
        {
            string sql = "SELECT COUNT(1) FROM [&#WednTablePrefix%&_Metas] WHERE [object_id]=@objectId AND [object_type]=@objectType AND [meta_key]=@key;";
            return (int)SqlHelper.ExecuteScalar(sql,
                new SqlParameter("@objectId", objectId),
                new SqlParameter("@objectType", objectType),
                new SqlParameter("@key", key)) > 0;
        }

        /// <summary>
        /// 取出当前拓展元数据表中的所有拓展元数据信息对象集合, 如果不存在则返回null;
        /// </summary>
        /// <returns>所有拓展元数据信息对象集合</returns>
        public List<Meta> SelectMetaList()
        {
            string sql = @"SELECT 
                                        [meta_id],
                                        [object_id],
                                        [object_type],
                                        [meta_key],
                                        [meta_value]
                                    FROM
                                        [&#WednTablePrefix%&_Metas];";
            return Select(sql);
        }

        /// <summary>
        /// 根据指定关联对象类型取出当前拓展元数据表中的所有拓展元数据信息对象集合, 如果不存在则返回null;
        /// </summary>
        /// <param name="objectType">关系对象类型(0:Post, 1:User, 2:Link, 3:Comment)</param>
        /// <returns>管理对象类型的所有拓展元数据信息对象集合</returns>
        public List<Meta> SelectMetaList(int objectType)
        {
            string sql = @"SELECT 
                                        [meta_id],
                                        [object_id],
                                        [object_type],
                                        [meta_key],
                                        [meta_value]
                                    FROM
                                        [&#WednTablePrefix%&_Metas]
                                    WHERE
                                        [object_type]=@Type;";
            return Select(sql, new SqlParameter("@Type", objectType));
        }

        /// <summary>
        /// 根据关系对象ID和关系对象类型取出当前类别下拓展元数据表中的所有拓展元数据信息对象集合, 如果不存在则返回null;
        /// </summary>
        /// <param name="objectId">关联对象ID</param>
        /// <param name="objectType">关系对象类型(0:Post, 1:User, 2:Link, 3:Comment)</param>
        /// <returns>指定对象的所有拓展元数据信息对象集合</returns>
        public List<Meta> SelectMetaList(int objectId, int objectType)
        {
            string sql = @"SELECT 
                                        [meta_id],
                                        [object_id],
                                        [object_type],
                                        [meta_key],
                                        [meta_value]
                                    FROM
                                        [&#WednTablePrefix%&_Metas]
                                    WHERE
                                        [object_type]=@Type;
                                    AND
                                        [object_id]=@Id";
            return Select(sql, new SqlParameter("@Type", objectType), new SqlParameter("@Id", objectId));
        }

        /// <summary>
        /// 根据ID取出当前拓展元数据表中的拓展元数据对象, 如果不存在则返回null;
        /// </summary>
        /// <param name="id">元数据ID</param>
        /// <returns>拓展元数据对象</returns>
        public Meta SelectMeta(int id)
        {
            string sql = @"SELECT 
                                        [meta_id],
                                        [object_id],
                                        [object_type],
                                        [meta_key],
                                        [meta_value]
                                    FROM
                                        [&#WednTablePrefix%&_Metas]
                                    WHERE
                                        [meta_id]=@Id;";
            List<Meta> list = Select(sql, new SqlParameter("@Id", id));
            return list != null ? list[0] : null;
        }

        /// <summary>
        /// 根据关联对象ID, 类型, 键取出当前拓展元数据表中的对应的选项值, 如果不存在则返回null;
        /// </summary>
        /// <param name="objectId">关联对象ID</param>
        /// <param name="objectType">关系对象类型(0:Post, 1:User, 2:Link, 3:Comment)</param>
        /// <param name="key">属性键</param>
        /// <returns>对应的选项值</returns>
        public string SelectValue(int objectId, int objectType, string key)
        {
            string sql = @"SELECT 
                                        [meta_value]
                                    FROM
                                        [&#WednTablePrefix%&_Metas]
                                     WHERE [object_id]=@objectId AND [object_type]=@objectType AND [meta_key]=@key;";
            object res = SqlHelper.ExecuteScalar(sql,
                new SqlParameter("@objectId", objectId),
                new SqlParameter("@objectType", objectType),
                new SqlParameter("@key", key));
            return res != null ? res.ToString() : null;
        }
        #region 私有操作方法

        #region select
        /// <summary>
        /// 私有选取数据方法, 如果不存在则返回null;
        /// </summary>
        /// <param name="sql">T-SQL语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>数据集合</returns>
        private static List<Meta> Select(string sql, params SqlParameter[] parameter)
        {
            List<Meta> list = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                if (reader.HasRows)
                {
                    list = new List<Meta>();
                    while (reader.Read())
                    {
                        Meta meta = new Meta()
                        {
                            Id = reader.GetInt32(0),
                            ObjectId = reader.GetInt32(1),
                            ObjectType = reader.GetInt32(2),
                            Key = reader.GetString(3),
                            Value = reader.IsDBNull(4) ? null : reader.GetString(4)
                        };
                        list.Add(meta);
                    }
                }
            }
            return list;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 私有执行写入数据库操作
        /// </summary>
        /// <param name="meta">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>受影响行数</returns>
        private static int ExecuteSql(Meta meta, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@ObjectId", meta.ObjectId),
                new SqlParameter("@ObjectType", meta.ObjectType),
                new SqlParameter("@Key", meta.Key),
                new SqlParameter("@Value", string.IsNullOrEmpty(meta.Value) ? System.DBNull.Value : (object)meta.Value),
                new SqlParameter("@Id", meta.Id));
        }
        #endregion

        #endregion
    }
}
